package com.yth.utils;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * SQL工具类
 *
 * @author yutianhong
 * @version 1.0
 * @since 2024/11/20 15:21
 */
public class SqlUtils {

    // 正则表达式用于匹配单行和多行注释
    private static final String singleLineCommentPattern = "--[^\r\n]*";
    private static final String multiLineCommentPattern = "/\\*.*?\\*/";

    private static final Pattern singleLinePattern = Pattern.compile(singleLineCommentPattern);
    private static final Pattern multiLinePattern = Pattern.compile(multiLineCommentPattern, Pattern.DOTALL);

    private static final SimpleDateFormat SQL_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private static final String BACKTICK = "`";
    private static final String[] ZERO_LENGTH_STRING_ARRAY = new String[0];

    /**
     * 去除名称中的反引号字符.
     *
     * @param name 包含反引号的名称
     * @return 去除反引号后的名称
     */
    public static String removeBackticks(String name) {
        if (name == null || name.length() < 2) {
            return name;
        }

        if (name.startsWith(BACKTICK) && name.endsWith(BACKTICK)) {
            return name.substring(1, name.length() - 1);
        }
        return name;
    }

    /**
     * 去除SQL中的注释
     *
     * @param sql 原始的SQL语句
     * @return 消除注释的SQL语句
     */
    public static String removeComment(String sql) {
        if (StringUtils.isBlank(sql)) {
            return sql;
        }
        // 创建匹配器并移除单行和多行注释
        Matcher singleLineMatcher = singleLinePattern.matcher(sql);
        sql = singleLineMatcher.replaceAll("");

        Matcher multiLineMatcher = multiLinePattern.matcher(sql);
        sql = multiLineMatcher.replaceAll("");

        return sql.trim(); // 返回去除注释后的SQL，并去掉首尾的多余空白
    }

    /**
     * 转义SQL参数数组，将每个元素转换为其SQL字符串表示形式。
     *
     * @param sqlArgs the array of SQL arguments to escape
     * @return an array of escaped SQL argument strings
     */
    public static String[] escapeSqlArg(Object[] sqlArgs) {
        if (sqlArgs == null) {
            return null;
        }
        if (sqlArgs.length == 0) {
            return ZERO_LENGTH_STRING_ARRAY;
        }
        String[] ret = new String[sqlArgs.length];
        for (int i = 0; i < sqlArgs.length; i++) {
            ret[i] = escapeSqlArg(sqlArgs[i]);
        }
        return ret;
    }

    public static String escapeSqlArg(Object sqlArg) {
        if (sqlArg == null) {
            return "NULL";
        } else if (sqlArg instanceof java.util.Date) {
            long timestamp = ((Date) sqlArg).getTime();
            return "'" + formatSqlTimestamp(timestamp) + "'";
        } else if (sqlArg instanceof Calendar) {
            Calendar cal = (Calendar) sqlArg;
            long timestamp = cal.getTime().getTime();
            return "'" + formatSqlTimestamp(timestamp) + "'";
        } else if (sqlArg instanceof Number) {
            return sqlArg.toString();
        } else {
            String sqlStringArg = sqlArg.toString();
            String transferred = sqlStringArg.replace("'", "''");
            return "'" + transferred + "'";
        }
    }

    private static String formatSqlTimestamp(long timestamp) {
        Date date = new Date(timestamp);
        return SQL_DATE_FORMAT.format(date);
    }

    /**
     * 转换SQL类型对象为对应的Java类型。
     *
     * @param obj 需要转换的SQL对象，可能是java.sql.Date, java.sql.Time,
     *            java.sql.Timestamp, Blob, Clob, 或 SQLXML。
     * @return 转换后的Java对象
     * @throws RuntimeException 如果发生SQLException，包含对象信息。
     */
    public static Object convertSqlTypeToJavaType(Object obj) {
        try {
            return convertSqlTypeToJavaTypeInner(obj);
        } catch (SQLException e) {
            String classType = obj == null ? null : obj.getClass().getName();
            throw new RuntimeException("数据转换异常, obj: " + obj + ", objClassType: " + classType, e);
        }
    }

    private static Object convertSqlTypeToJavaTypeInner(Object obj) throws SQLException {
        if (obj == null) {
            return null;
        }
        if (obj instanceof java.sql.Date) {
            java.sql.Date sqlDate = (java.sql.Date) obj;
            return new java.util.Date(sqlDate.getTime());
        } else if (obj instanceof java.sql.Time) {
            java.sql.Time sqlTime = (java.sql.Time) obj;
            return new java.util.Date(sqlTime.getTime());
        } else if (obj instanceof java.sql.Timestamp) {
            java.sql.Timestamp sqlTimestamp = (java.sql.Timestamp) obj;
            return new java.util.Date(sqlTimestamp.getTime());
        } else if (obj instanceof Blob) {
            Blob blob = (Blob) obj;
            obj = blob.getBytes(1, (int) blob.length());
        } else if (obj instanceof Clob) {
            Clob clob = (Clob) obj;
            obj = clob.getSubString(1, (int) clob.length());
        } else if (obj instanceof Array) {
            Array array = (Array) obj;
            Object result = array.getArray();
            array.free();
            return result;
        } else if (obj instanceof SQLXML) {
            SQLXML sqlxml = (SQLXML) obj;
            try {
                return sqlxml.getString();
            } finally {
                sqlxml.free();
            }
        }
        return obj;
    }

    /**
     * 获取SQL操作涉及的表
     *
     * @param sql    DML类型的SQL语句
     * @param dbType 数据库类型
     * @return SQL中涉及的表名称集合
     */
    public static Set<String> getTableNameList(String sql, String dbType) {
        if (StringUtils.isBlank(sql)) {
            return Collections.emptySet();
        }
        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
        if (CollectionUtils.isEmpty(statementList)) {
            return Collections.emptySet();
        }
        Set<String> result = new HashSet<>();
        for (SQLStatement sqlStatement : statementList) {
            Set<String> tableNames = getTableNameList(sqlStatement);
            result.addAll(tableNames);
        }
        return result.stream().map(SqlUtils::removeBackticks).collect(Collectors.toSet());
    }

    public static Set<String> getTableNameList(String sql) {
        return getTableNameList(sql, DbType.mysql.name());
    }

    /**
     * 判断SQL类型是否符合指定集合。
     *
     * @param sql     SQL语句
     * @param dbType  数据库类型
     * @param sqlType SQL类型集合
     * @return 如果SQL语句类型符合指定类型集合，返回true；否则返回false
     */
    public static boolean predicateSqlType(String sql, String dbType, Set<String> sqlType) {
        if (StringUtils.isBlank(sql) || CollectionUtils.isEmpty(sqlType)) {
            return false;
        }
        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
        if (CollectionUtils.isEmpty(statementList)) {
            return false;
        }
        for (SQLStatement sqlStatement : statementList) {
            if (!sqlType.contains(sqlStatementType(sqlStatement))) {
                return false;
            }
        }
        return true;
    }

    public static boolean isSingleSelectSQL(String sql) {
        return isSingleSelectSQL(sql, DbType.mysql.name());
    }

    /**
     * 判断是不是单条SELECT语句。
     *
     * @param sql    SQL语句
     * @param dbType 数据库类型
     * @return 如果是单条SELECT语句返回true，否则返回false
     */
    public static boolean isSingleSelectSQL(String sql, String dbType) {
        if (StringUtils.isBlank(sql)) {
            return false;
        }
        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
        if (CollectionUtils.isEmpty(statementList)) {
            return false;
        }
        if (statementList.size() > 1) {
            return false;
        }
        SQLStatement sqlStatement = statementList.get(0);
        String type = sqlStatementType(sqlStatement);
        return "SELECT".equals(type);
    }

    /**
     * 判断是不是SELECT语句。
     *
     * @param sql SQL语句
     * @return 如果是SELECT语句返回true，否则返回false
     */
    public static boolean isQueryOperationSQL(String sql) {
        return predicateSqlType(sql, DbType.mysql.name(), Collections.singleton("SELECT"));
    }

    /**
     * 判断是不是写操作（UPDATE、INSERT、DELETE）。
     *
     * @param sql SQL语句
     * @return 如果是写操作返回true，否则返回false
     */
    public static boolean isWriteOperationSQL(String sql) {
        return predicateSqlType(sql, DbType.mysql.name(), new HashSet<>(Arrays.asList("UPDATE", "INSERT", "DELETE")));
    }

    /**
     * 判断是不是DML语句。
     *
     * @param sql SQL语句
     * @return 如果是DML语句返回true，否则返回false
     */
    public static boolean isDML(String sql) {
        return predicateSqlType(sql, DbType.mysql.name(), new HashSet<>(Arrays.asList("SELECT", "INSERT", "UPDATE", "DELETE")));
    }

    private static Set<String> getTableNameList(SQLStatement sqlStatement) {
        if (sqlStatement instanceof SQLSelectStatement) {
            SQLSelectStatement selectStatement = (SQLSelectStatement) sqlStatement;
            SQLSelectQuery sqlSelectQuery = selectStatement.getSelect().getQuery();
            return getTableNameList(sqlSelectQuery);
        } else if (sqlStatement instanceof SQLUpdateStatement) {
            SQLUpdateStatement updateStatement = (SQLUpdateStatement) sqlStatement;
            SQLName tableName = updateStatement.getTableName();
            if (tableName != null) {
                return Collections.singleton(tableName.getSimpleName());
            }
        } else if (sqlStatement instanceof SQLDeleteStatement) {
            SQLDeleteStatement deleteStatement = (SQLDeleteStatement) sqlStatement;
            SQLName tableName = deleteStatement.getTableName();
            if (tableName != null) {
                return Collections.singleton(tableName.getSimpleName());
            }
        } else if (sqlStatement instanceof SQLInsertStatement) {
            SQLInsertStatement insertStatement = (SQLInsertStatement) sqlStatement;
            SQLName tableName = insertStatement.getTableName();
            if (tableName != null) {
                return Collections.singleton(tableName.getSimpleName());
            }
        }
        return Collections.emptySet();
    }

    private static Set<String> getTableNameList(SQLSelectQuery sqlSelectQuery) {
        if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
            SQLTableSource sqlTableSource = queryBlock.getFrom();
            return getTableNameList(sqlTableSource);
        } else if (sqlSelectQuery instanceof SQLUnionQuery) {
            SQLUnionQuery unionQuery = (SQLUnionQuery) sqlSelectQuery;
            Set<String> left = getTableNameList(unionQuery.getLeft());
            Set<String> right = getTableNameList(unionQuery.getRight());
            return mergeSet(left, right);
        }
        return Collections.emptySet();
    }

    private static Set<String> getTableNameList(SQLTableSource from) {
        if (from instanceof SQLExprTableSource) {
            SQLExprTableSource sqlExprTableSource = (SQLExprTableSource) from;
            String tableName = sqlExprTableSource.getTableName();
            return Collections.singleton(tableName);
        } else if (from instanceof SQLJoinTableSource) {
            SQLJoinTableSource joinObject = (SQLJoinTableSource) from;
            Set<String> left = getTableNameList(joinObject.getLeft());
            Set<String> right = getTableNameList(joinObject.getRight());
            return mergeSet(left, right);
        } else if (from instanceof SQLSubqueryTableSource) {
            SQLSubqueryTableSource sqlSubqueryTableSource = (SQLSubqueryTableSource) from;
            SQLSelect subSelectObject = sqlSubqueryTableSource.getSelect();
            return getTableNameList(subSelectObject.getQuery());
        } else if (from instanceof SQLUnionQueryTableSource) {
            SQLUnionQueryTableSource unionFrom = (SQLUnionQueryTableSource) from;
            SQLUnionQuery union = unionFrom.getUnion();
            Set<String> left = getTableNameList(union.getLeft());
            Set<String> right = getTableNameList(union.getRight());
            return mergeSet(left, right);
        }
        return Collections.emptySet();
    }

    private static Set<String> mergeSet(Set<String> leftTableNames, Set<String> rightTableNames) {
        Set<String> result = new HashSet<>(leftTableNames);
        result.addAll(rightTableNames);
        return result;
    }

    private static String sqlStatementType(SQLStatement sqlStatement) {
        if (sqlStatement instanceof SQLSelectStatement) {
            return "SELECT";
        } else if (sqlStatement instanceof SQLUpdateStatement) {
            return "UPDATE";
        } else if (sqlStatement instanceof SQLDeleteStatement) {
            return "DELETE";
        } else if (sqlStatement instanceof SQLInsertStatement) {
            return "INSERT";
        } else {
            return "NOT DML";
        }
    }

    private SqlUtils() {
    }

}
